package com.conote.mess;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.LinkedHashSet;
import java.util.Set;

public class Json2Excel {

    public static void convert(String jsonString, String outputPath) throws IOException {
        ObjectMapper mapper = new ObjectMapper();

        try (Workbook workbook = new XSSFWorkbook()) {
            // 创建支持中文的字体样式
            Font chineseFont = workbook.createFont();
            chineseFont.setFontName("微软雅黑");

            JsonNode rootNode = mapper.readTree(jsonString);

            if (!rootNode.isArray()) {
                throw new IllegalArgumentException("JSON should be an array");
            }

            // 添加映射关系
            String mapping = """
                {
                  "id": "编号",
                  "name": "姓名",
                  "age": "年龄",
                  "email": "邮箱",
                  "city": "城市",
                  "gender": "性别",
                  "phone": "手机号",
                  "occupation": "职业",
                  "department": "部门",
                  "salary": "薪资",
                  "is_active": "是否激活",
                  "date_joined": "入职日期"
                }
                """;
            ObjectNode mappingNode = (ObjectNode) mapper.readTree(mapping);

            Set<String> headers = new LinkedHashSet<>();
            for (JsonNode node : rootNode) {
                node.fieldNames().forEachRemaining(headers::add);
            }

            Sheet sheet = workbook.createSheet("Data");

            // 创建表头行
            Row headerRow = sheet.createRow(0);
            int colIdx = 0;
            for (String header : headers) {
                Cell cell = headerRow.createCell(colIdx++);
                // 使用映射关系设置表头
                String mappedHeader = mappingNode.has(header) ? mappingNode.get(header).asText() : header;
                cell.setCellValue(mappedHeader);
                applyChineseStyle(workbook, cell, chineseFont);
            }

            // 填充数据
            int rowIdx = 1;
            for (JsonNode node : rootNode) {
                Row row = sheet.createRow(rowIdx++);
                colIdx = 0;
                for (String header : headers) {
                    JsonNode valueNode = node.get(header);
                    Cell cell = row.createCell(colIdx++);
                    setCellValue(workbook, cell, valueNode, chineseFont);
                }
            }

            // 自动调整列宽（考虑中文）
            for (int i = 0; i < headers.size(); i++) {
                sheet.autoSizeColumn(i);
                // 补偿中文宽度计算误差（单位：1/256个字符宽度）
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 1024);
            }

            try (FileOutputStream fos = new FileOutputStream(outputPath)) {
                workbook.write(fos);
            }
        }
    }

    private static void setCellValue(Workbook workbook, Cell cell, JsonNode valueNode, Font font) {
        CellStyle style = workbook.createCellStyle();
        style.setFont(font);
        // 添加水平和垂直居中对齐
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        if (valueNode == null || valueNode.isNull()) {
            cell.setCellValue("");
            cell.setCellStyle(style);
            return;
        }

        if (valueNode.isNumber()) {
            cell.setCellValue(valueNode.asDouble());
            style.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00"));
        } else if (valueNode.isBoolean()) {
            cell.setCellValue(valueNode.asBoolean());
        } else if (valueNode.isTextual()) {
            String text = valueNode.asText();
            // 日期格式处理示例
            if (text.matches("\\d{4}-\\d{2}-\\d{2}")) {
                CellStyle dateStyle = workbook.createCellStyle();
                dateStyle.cloneStyleFrom(style);
                dateStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd"));
                cell.setCellStyle(dateStyle);
                cell.setCellValue(text); // 建议实际使用Date对象
            } else {
                cell.setCellValue(text);
            }
        } else {
            cell.setCellValue(valueNode.toString());
        }
        cell.setCellStyle(style);
    }

    private static void applyChineseStyle(Workbook workbook, Cell cell, Font font) {
        CellStyle style = workbook.createCellStyle();
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        cell.setCellStyle(style);
    }

    public static void main(String[] args) {
        String json = """
                [
                    {
                        "id": 1,
                        "name": "Alice",
                        "age": 25,
                        "email": "alice@example.com",
                        "city": "New York",
                        "gender": "Female",
                        "phone": "123-456-7890",
                        "occupation": "Engineer",
                        "department": "Software",
                        "salary": 85000,
                        "is_active": true,
                        "date_joined": "2021-05-15"
                    },
                    {
                        "id": 2,
                        "name": "Bob",
                        "age": 30,
                        "email": "bob@example.com",
                        "city": "Los Angeles",
                        "gender": "Male",
                        "phone": "234-567-8901",
                        "occupation": "Designer",
                        "department": "Creative",
                        "salary": 78000,
                        "is_active": true,
                        "date_joined": "2019-08-20"
                    },
                    {
                        "id": 3,
                        "name": "Charlie",
                        "age": 35,
                        "email": "charlie@example.com",
                        "city": "Chicago",
                        "gender": "Male",
                        "phone": "345-678-9012",
                        "occupation": "Manager",
                        "department": "Operations",
                        "salary": 95000,
                        "is_active": true,
                        "date_joined": "2017-03-10"
                    },
                    {
                        "id": 4,
                        "name": "Diana",
                        "age": 28,
                        "email": "diana@example.com",
                        "city": "Houston",
                        "gender": "Female",
                        "phone": "456-789-0123",
                        "occupation": "Analyst",
                        "department": "Finance",
                        "salary": 72000,
                        "is_active": false,
                        "date_joined": "2020-06-18"
                    },
                    {
                        "id": 5,
                        "name": "Eve",
                        "age": 22,
                        "email": "eve@example.com",
                        "city": "Phoenix",
                        "gender": "Female",
                        "phone": "567-890-1234",
                        "occupation": "Intern",
                        "department": "Marketing",
                        "salary": 45000,
                        "is_active": true,
                        "date_joined": "2023-09-01"
                    }
                ]
                """;

        try {
            convert(json, "output.xlsx");
            System.out.println("转换成功！");
        } catch (IOException e) {
            System.err.println("转换失败: " + e.getMessage());
        }
    }
}
